<?php
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel.php");
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel/Writer/Excel2007.php");

class Silk_Zgb_Block_Report_Ordersum extends Mage_Core_Block_Template
{
    public function getTeamCustomer()
    {
        $collection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToFilter('group_id', array('eq'=>4));

        $ids = array(0);
        foreach($collection as $customer)
        {
            $ids[] = $customer->getId();
        }
        return $ids;
    }

    public function getItemCollection()
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $startDate = $this->getRequest()->getParam('start_date');
        $endDate = $this->getRequest()->getParam('end_date');
        $websiteId = 0;
        $session = Mage::getSingleton('admin/session');
        $is_salt = $this->getRequest()->getParam('is_salt');

        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
        }

        $sql = "SELECT website_id,website_name FROM zgb_order GROUP BY website_id";

        $query = $db->query($sql);
        $rs = $query->fetchAll();

        $regions = array();
        foreach($rs as $item)
        {
            if($websiteId && $websiteId!=$item['website_id']) continue;
            $regions[$item['website_id']] = array('name'=>$item['website_name']);
        }

        $datesql = '';
        if($startDate)
        {
            $startDate = "{$startDate} 00:00:00";
            $timestamp = strtotime($startDate);
            $startDate = date('Y-m-d H:i:s', $timestamp);
            $datesql .= " and created_at>= '{$startDate}'";
        }

        if($endDate)
        {
            $endDate = "{$endDate} 23:59:59";
            $timestamp = strtotime($endDate);
            $endDate = date('Y-m-d H:i:s', $timestamp);
            $datesql .= " and created_at<= '{$endDate}'";
        } 

        $teams = implode(',',$this->getTeamCustomer());
        
        $salt = "";
        if($is_salt)
        {
            if($is_salt == 1)
            {
                $salt = " and is_salt=0";
            }
            else
            {
                $salt = " and is_salt=1";
            }
        }

        $statues = "'pending','processing','complete','complete_ship','complete_delivering','complete_order'";
        foreach($regions as $key=>$data)
        {
            $sql = "select count(*) from zgb_store where is_active=1 and website_id='{$key}' and entity_id not in({$teams}) {$datesql} ";
            $query = $db->query($sql);
            $regions[$key]['new_customer'] = $query->fetchColumn();

            $sql = "select count(*) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and is_first=1 and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['new_customer_order'] = $query->fetchColumn();

            $sql = "select count(*) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and is_first=0 and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['old_customer_order'] = $query->fetchColumn();

            $sql = "select count(*) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['order_count'] = $query->fetchColumn();

            $sql = "select count(distinct(customer_id)) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and is_first=1 and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['new_customer_count'] = $query->fetchColumn();

            $sql = "select distinct(customer_id) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and is_first=1 and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $rs = $query->fetchAll();
            $ids = array();
            foreach($rs as $item)
            {
                $ids[] = $item['customer_id'];
            }

            $idsql = "";
            if($ids) $idsql = "and customer_id not in (".implode(',',$ids).")";

            $sql = "select count(distinct(customer_id)) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and is_first=0 and status in ({$statues}) {$idsql} {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['old_customer_count'] = $query->fetchColumn();

            $sql = "select count(distinct(customer_id)) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and status in ({$statues}) {$datesql}";
            $query = $db->query($sql);
            $regions[$key]['customer_count'] = $query->fetchColumn();


            //if($is_salt)
            //{

                $sql = "select sum(grand_total) from zgb_order where website_id='{$key}' {$salt} and customer_id not in({$teams}) and status in ({$statues}) {$datesql}";
            //}
            //else
            //{
            //   $sql = "select sum(grand_total_ex) from zgb_order where website_id='{$key}' and customer_id not in({$teams}) and status in ('pending','processing','complete') {$datesql}";
            //}
            
            $query = $db->query($sql);
            $regions[$key]['order_total'] = $query->fetchColumn();
        }

        return $regions;
    }
}